﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace App
{
    class Service
    {
        static Entities db = new Entities();
        //客户
        public static void Cust(CB cb)
        {
            var list = db.T_Custom.ToList();
            cb.DataSource = list;
            cb.DisplayMember = "custom";
            cb.ValueMember = "CID";
            cb.SelectedIndex = -1;
        }

         //码段
        public static void Yard(CB cb)
        {
            var Yardlist = db.T_YardDesc;
            cb.DataSource = Yardlist.ToList();
            cb.DisplayMember = "yardTypeCHN";
            cb.ValueMember = "yardID";
            cb.SelectedIndex = -1;
        }

        //绑定指令人
        public static void CommandName(CB cb)
        {
            var cName = db.T_Stuff.ToList();
            cb.DataSource = cName;
            cb.DisplayMember = "nameEn";
            cb.ValueMember = "stID";
            cb.SelectedIndex = -1;
        }

         //码制
        public static void Size2(CB cb)
        {
            var Sizelist = db.T_SizeDesc;
            cb.DataSource = Sizelist.ToList();
            cb.DisplayMember = "mbEn";
            cb.ValueMember = "szID";
            cb.SelectedIndex = -1;
        }

        //化学物质
        public static void Chymist(CB cb)
        {
            var chlist1 = db.T_Chymist.ToList();
            cb.DataSource = chlist1;
            cb.DisplayMember = "mDesc";
            cb.ValueMember = "id";
            cb.SelectedIndex = -1;

        }

        //品牌
        public static void Brand(CB cb)
        {
            var brandlist = db.T_BrandDesc.ToList();
            cb.DataSource = brandlist;
            cb.DisplayMember = "brandName";
            cb.ValueMember = "brandID";
            cb.SelectedIndex = -1;
        }

        //最终客户
        public static void CustDept(CB cb)
        {
            var custDesclist = db.T_CustDeptDesc.ToList();
            cb.DataSource = custDesclist;
            cb.DisplayMember = "custDept";
            cb.ValueMember = "id";
            cb.SelectedIndex = -1;
        }

        //工厂
        public static void Factory(CB cb)
        {
            var factorylist = db.T_Factory.Where(a=>a.Kind=="工厂"&&a.factoryStatus=="启用").ToList();
            cb.DataSource = factorylist;
            cb.DisplayMember = "factory";
            cb.ValueMember = "fID";
            cb.SelectedIndex = -1;
        }

         //样品性质
        public static void Property(CB cb)
        {
            var samplepropertylist = db.T_PropertyDesc.ToList();
            cb.DataSource = samplepropertylist;
            cb.DisplayMember = "pDesc";
            cb.ValueMember = "sID";
            cb.SelectedIndex = -1;
        }

         //内里材料
        public static void lMaterial(CB cb)
        {
            var lMateriallist = db.T_MaterialDesc.Where(a => a.materialType == "内里").ToList();
            cb.DataSource = lMateriallist;
            cb.DisplayMember = "materialEN";
            cb.ValueMember = "materialID";
            cb.SelectedIndex = -1;
        }

         //大底底厂
        public static void SoleFactory(CB cb)
        {
            var solefactorylist = db.T_Factory.Where(a => a.Kind == "大底工厂" && a.factoryStatus == "启用").ToList();
            cb.DataSource = solefactorylist;
            cb.DisplayMember = "factory";
            cb.ValueMember = "fID";
            cb.SelectedIndex = -1;
        }
        
        //大底材料
        public static void SoleMaterial(CB cb)
        {
            var solemateriallist = db.T_MaterialDesc.Where(a => a.materialType == "大底").ToList();
            cb.DataSource = solemateriallist;
            cb.DisplayMember = "materialEN";
            cb.ValueMember = "materialID";
            cb.SelectedIndex = -1;
        }

        //组
        public static void Group(CB cb)
        {
            var sGrouplist = db.T_GroupDesc.ToList();
            cb.DataSource = sGrouplist;
            cb.DisplayMember = "GroupName";
            cb.ValueMember = "GroupName";
            cb.SelectedIndex = -1;
        }

        //库存位置
        public static void Location(CB cb)
        {
            var locationlist = db.T_Store.ToList();
            cb.DataSource = locationlist;
            cb.DisplayMember = "location";
            cb.ValueMember = "kID";
            cb.SelectedIndex = -1;
        }
        //备注内容
        public static void Txtremark(CB cb)
        {
            var txtremarklist = db.T_SendDetail.ToList();
            cb.DataSource = txtremarklist;
            cb.DisplayMember = "remark";
            cb.SelectedIndex = -1;
        }
        
        //性质
        public static void Ktype(CB cb)
        {
            var ktypelist = db.T_Store.ToList();
            cb.DataSource = ktypelist;
            cb.DisplayMember = "kType";
            cb.SelectedIndex = -1;
        }
        //工厂简称
        public static void Sfactory(CB cb)
        {
            var sfactorylist = db.T_Factory.ToList();
            cb.DataSource = sfactorylist;
            cb.DisplayMember = "factory";
            cb.SelectedIndex = -1;
        }
        //现场QC
        //public static void XQC(CB cb)
        //{
        //    var XQClist = db.T_QC.ToList();
        //    cb.DataSource = XQClist;
        //    cb.DisplayMember = "name";
        //    cb.ValueMember = "stID";
        //    cb.SelectedIndex = -1;
        //}
        //跟单QC
        //public static void GQC(CB cb)
        //{
        //    var GQClist = db.T_QC.ToList();
        //    cb.DataSource = GQClist;
        //    cb.DisplayMember = "name";
        //    cb.ValueMember = "stID";
        //    cb.SelectedIndex = -1;
        //}
        
            //开票工厂
        public static void kpFactory(CB cb)
            {
                var kpFactorylist = db.T_KpFactory.ToList();
                cb.DataSource = kpFactorylist;
                cb.DisplayMember = "kpFactory";
                cb.SelectedIndex = -1;
            }
        //交货地点
        public static void DiliverPlace(CB cb)
        {
            var diliverPlacelist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "diliverPlace").ToList();
            cb.DataSource = diliverPlacelist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //交货方式
        public static void DiliverMode(CB cb)
        {
            var diliverModelist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "diliverMode").ToList();
            cb.DataSource = diliverModelist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //交货条件
        public static void DiliverCondition(CB cb)
        {
            var diliverConditionlist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "require").ToList();
            cb.DataSource = diliverConditionlist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //结算方式
        public static void PayMode(CB cb)
        {
            var payModelist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "payMode").ToList();
            cb.DataSource = payModelist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //包装标准
        public static void Pack(CB cb)
        {
            var packlist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "pack").ToList();
            cb.DataSource = packlist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //验收
        public static void ChkAcp(CB cb)
        {
            var chkAcplist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "chkAcp").ToList();
            cb.DataSource = chkAcplist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //其他
        public static void Others(CB cb)
        {
            var otherslist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "others").ToList();
            cb.DataSource = otherslist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //附件
        public static void Memo(CB cb)
        {
            var Memolist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "Memo").ToList();
            cb.DataSource = Memolist;
            cb.DisplayMember = "id";
            cb.ValueMember = "valueDesc";
            cb.SelectedIndex = -1;
        }
        //解决纠纷方式
        public static void Resolve(CB cb)
        {
            var resolvelist = db.T_InCtrDefaultInfo.Where(a => a.fieldNameEN == "resolve").ToList();
            cb.DataSource = resolvelist;
            cb.DisplayMember = "valueDesc";
            cb.ValueMember = "id";
            cb.SelectedIndex = -1;
        }
       
      
        //季节
        public static void Season(CB cb)
        {
            var Seasonlist = db.T_SeasonDesc.ToList();
            cb.DataSource = Seasonlist;
            cb.DisplayMember = "Season";
            cb.SelectedIndex = -1;
        }
        //发货国
        public static void Origin(CB cb)
        {
            var originlist = db.T_CountryDesc.ToList();
            cb.DataSource = originlist;
            cb.DisplayMember = "countryCHN";
            cb.ValueMember = "countryID";
            cb.SelectedIndex = -1;
        }
        //发货港
        public static void ShipPort(CB cb)
        {
            var shipPortlist = db.T_CityDesc.ToList();
            cb.DataSource = shipPortlist;
            cb.DisplayMember = "cityCHN";
            cb.SelectedIndex = -1;
        }

        //地区
        public static void Area(CB cb)
        {
            var arealist = db.T_AreaDesc.ToList();
            cb.DataSource = arealist;
            cb.DisplayMember = "areaCHN";
            //cb.ValueMember = "";
            cb.SelectedIndex = -1;
        }


        //目的港
        public static void DstPost(CB cb)
        {
            var dstPostlist = db.T_CityDesc.ToList();
            cb.DataSource = dstPostlist;
            cb.DisplayMember = "cityCHN";
            cb.SelectedIndex = -1;
        }
        //业务员
        //public static void YName(CB cb)
        //{
        //    var yNamelist = db.T_yName.ToList();
        //    cb.DataSource = yNamelist;
        //    cb.DisplayMember = "name";
        //    cb.ValueMember = "stID";
        //    cb.SelectedIndex = -1;
        //}
       //LC通知行
        public static void LC(CB cb)
        {
            var lclist = db.T_LCDesc.ToList();
            cb.DataSource = lclist;
            cb.DisplayMember = "LCitem";
            cb.SelectedIndex = -1;
        }
        //外合同
       
        //内价格
        public static void ICurSystem(CB cb)
        {
            var icurSystemlist = db.T_CurrencyDesc.ToList();
            cb.DataSource = icurSystemlist;
            cb.DisplayMember = "currencyEN";
            cb.SelectedIndex = -1;
        }
        //外价格
        public static void CurSystem(CB cb)
        {
            var curSystemlist = db.T_CurrencyDesc.ToList();
            cb.DataSource = curSystemlist;
            cb.DisplayMember = "currencyEn";
            cb.SelectedIndex = -1;
        }

        //寄出单备注内容
        public static void CBRemark(CB cb)
        {
            var remarkList = db.T_TableInfo.ToList();
            cb.DataSource = remarkList;
            cb.DisplayMember = "fieldDesc";
            cb.ValueMember = "fieldName";
            cb.SelectedIndex = -1;
        }

        //业务员
        public static void Buyer(CB cb)
        {
            var bulist = db.T_Stuff.Where(a => a.sPosition == 31).ToList();
            cb.DataSource = bulist;
            cb.DisplayMember = "name";
            cb.ValueMember = "stID";
            cb.SelectedIndex = -1;
        }

        //QC
        public static void QC(CB cb)
        {
            var qclist = db.T_Stuff.Where(a => a.sPosition == 18).ToList();
            cb.DataSource = qclist;
            cb.DisplayMember = "name";
            cb.ValueMember = "stID";
            cb.SelectedIndex = -1;
        }
    }

    class Excel
    {
        public static void ExcelSave (DataGridView dgv)
        { 
        if(dgv.RowCount<=0)
        {
            MessageBox.Show("缺少可以导出的数据！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
            return;
        }
            string saveFileName=string.Empty;
            SaveFileDialog sfd=new SaveFileDialog();
            sfd.DefaultExt="xlsx";
            sfd.Filter="Excel文件|*.xlsx";
            sfd.FileName=DateTime.Now.ToString("yyyy-MM-dd");
            sfd.ShowDialog();
            saveFileName=sfd.FileName;
            if(saveFileName.IndexOf(".")<0)
            {
                return;
            }
            Microsoft.Office.Interop.Excel.Application myExcelApp=new Microsoft.Office.Interop.Excel.Application();
            if(myExcelApp==null)
            {
                MessageBox.Show("无法创建Excel，可能您未安装Excel","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks=myExcelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Sheets sheets=workbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet=(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
            Microsoft.Office.Interop.Excel.Range range;
            object oMis=System.Reflection.Missing.Value;
            //显示为文本格式
            range=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[dgv.RowCount+1,dgv.ColumnCount]);
            range.NumberFormatLocal="@";
            //读入数据
            for (int i=0; i<dgv.ColumnCount;i++ )
            {
                worksheet.Cells [1, i+1]=dgv.Columns [i].HeaderText.ToString().Trim();
            }
            for (int r=0; r<dgv.RowCount;r++ )
            {
                for (int i=0; i<dgv.ColumnCount;i++ )
                {
                    worksheet.Cells [r+2, i+1]=dgv.Rows [r].Cells [i].Value.ToString().Trim();
                }
            }
            range=worksheet.get_Range( worksheet.Cells [1, 1], worksheet.Cells [dgv.RowCount+1, dgv.ColumnCount] );
            range.Columns.AutoFit();
            range.RowHeight=18;
            range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            //保存
            if (saveFileName!=string.Empty)
            {
                try
                {
                    workbook.Saved=true;
                    workbook.SaveCopyAs( saveFileName );
                }
                catch (Exception ex)
                {
                    MessageBox.Show( "导出文件时出错，文件可能正被打开！\n"+ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information );
                }
            }
            else
            {
                MessageBox.Show( "文件名不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information );
            }
            myExcelApp.Visible=false;
            myExcelApp.Quit();
            GC.Collect();//垃圾回收
        }
    }
}
